# -*- coding: utf-8 -*-

import os, sys, time, datetime, sqlite3

class SQLiteDB:
    def __init__(self):
        #makes a ramdb
        self.ramdb = sqlite3.connect(u':memory:')
        
    def MakeTables(self):
        #function to create the db structure in a new db
        cursor = self.ramdb.cursor()
        cursor.execute(u'''create table bookies (bookieid integer primary key, name text, balance real, revenue real, profit real, hasactivebonus integer, bonusrevenuecondition real, bonusoddscondition real, bonusrevenue real, username text, password text)''')
        cursor.execute(u'''create table transactions (transactionid integer primary key, bookieid integer, datetime text, money real, category integer)''')
        cursor.execute(u'''create table picks (pickid integer primary key, bookieid integer, datetime text, datetime_begin text, category integer, sport text, league text, hometeam text, awayteam text, picktype text, player text, pick text, odds text, odds_multi text, reason text, result integer, score text)''')
        cursor.execute(u'''create table bets (betid integer primary key, bookieid, transactionids text, datetime text, bettypeid integer, pickids text, category integer, odds real, stake real, result integer, return real)''')
        cursor.execute(u'''create table categories (categoryid integer primary key, category text)''')
        cursor.execute(u'''create table sportsleagues (sportleagueid integer primary key, sport text, league text)''')
        cursor.execute(u'''create table sportsbettypes (sportbettypeid integer primary key, sport text, bettype text)''')
        cursor.execute(u'''create table bettypes (bettypeid integer primary key, bettype text)''')
        self.ramdb.commit()
        self.CloseCursor(cursor)

    def CategoryETCData(self):
        #function that inserts preset data for categories etc.
        cursor = self.ramdb.cursor()
        category = [(None,u'Normal',),(None,u'Kombi',),(None,u'Monsterkombi',),(None,u'Sonstwasprojekt',)]
        for i in category:
            cursor.execute(u'''insert into categories values (?,?)''',i)
        sportleague = [(None,u'Fußball',u'1. Bundesliga',),(None,u'Fußball',u'2. Bundesliga',),(None,u'Fußball',u'3. Bundesliga',),(None,u'Fußball',u'Champions League',),(None,u'Fußball',u'Euro League',),(None,u'Fußball',u'UK Premier League',),(None,u'Basketball',u'NBA',),(None,u'Basketball',u'College Basketball',),(None,u'American Football',u'NFL',),(None,u'American Football',u'College Football',),(None,u'Eishockey',u'NHL',),(None,u'Baseball',u'MLB',)]
        for i in sportleague:
            cursor.execute(u'''insert into sportsleagues values (?,?,?)''',i)
        sportbettype =[(None,u'Fußball',u'3-Weg',),(None,u'Fußball',u'2-Weg',),(None,u'Fußball',u'Asiatisches Handicap',),(None,u'Fußball',u'Over/Under',),(None,u'Fußball',u'Ergebnis',),(None,u'Fußball',u'Spieler Spezial',),(None,u'Basketball',u'2-Weg',),(None,u'Basketball',u'Spread',),(None,u'Basketball',u'Over/under',),(None,u'Basketball',u'Spieler Spezial',),(None,u'American Football',u'2-Weg',),(None,u'American Football',u'Spread',),(None,u'American Football',u'Over/under',),(None,u'Eishockey',u'3-Weg',),(None,u'Eishockey',u'2-Weg DNB',),(None,u'Eishockey',u'2-Weg OT inkl.',),(None,u'Eishockey',u'PL (+-1,5)',),(None,u'Eishockey',u'PL (+-1,5) OT inkl.',),(None,u'Eishockey',u'Over/under',),(None,u'Eishockey',u'Over/under OT inkl.',),(None,u'Baseball',u'2-Weg',),(None,u'Baseball',u'RL (+-1,5)',),(None,u'Baseball',u'Over/under',)]
        for i in sportbettype:
            cursor.execute(u'''insert into sportsbettypes values (?,?,?)''',i)
        bettype = [(None,u'Einzelwette'),(None,u'Kombiwette'),(None,u'Systemwette')]
        for i in bettype:
            cursor.execute(u'''insert into bettypes values (?,?)''',i)
        self.ramdb.commit()
        #print 'Inserted Data'
        self.CloseCursor(cursor)
        
    def TestData(self):
        #function to insert preset testdata
        cursor = self.ramdb.cursor()
        bookies = [(None,u'Pinnacle',55.00,50.00,5.00,0,0,0,0,u'bg186867',u'hättestewohlgerne',),(None,u'bwin',20.00,0.00,0.00,1,60,1,0,u'bg1404',u'hättestewohlgerne',),(None,u'www.betoto.com',40.00,0.00,0.00,1,120,1,0,u'bg4211',u'hättestewohlgerne',),(None,u'www.sportingbet.com',200.00,0.00,0.00,1,1000,1.7,0,u'bg1404',u'hättestewohlgerne',)]
        for i in bookies:
            cursor.execute(u'''insert into bookies values (?,?,?,?,?,?,?,?,?,?,?)''',i)
        #print 'Inserted Testdata bookies'
        transactions = [(None,1,datetime.datetime.today(),50,u'Einzahlung',),(None,1,datetime.datetime.today(),-5,u'Wetteinsatz',),(None,1,datetime.datetime.today(),-5,u'Wetteinsatz',),(None,1,datetime.datetime.today(),-10,u'Wetteinsatz',),(None,1,datetime.datetime.today(),-20,u'Wetteinsatz',),(None,1,datetime.datetime.today(),-5,u'Wetteinsatz',),(None,1,datetime.datetime.today(),-5,u'Wetteinsatz',),(None,2,datetime.datetime.today(),10,u'Einzahlung',),(None,2,datetime.datetime.today(),10,u'Bonus',),(None,3,datetime.datetime.today(),20,u'Einzahlung',),(None,3,datetime.datetime.today(),20,u'Bonus',),(None,4,datetime.datetime.today(),100,u'Einzahlung',),(None,4,datetime.datetime.today(),100,u'Einzahlung',),(None,1,datetime.datetime.today(),10,u'Wettgewinn',),(None,1,datetime.datetime.today(),20,u'Wettgewinn',),(None,1,datetime.datetime.today(),15,u'Wettgewinn',),(None,1,datetime.datetime.today(),10,u'Wettgewinn',)]
        for i in transactions:
            cursor.execute(u'''insert into transactions values (?,?,?,?,?)''',i)
        #print 'Inserted Testdata transactions'
        picks = [(None,1,datetime.datetime.today(),datetime.datetime.today(),u'',u'Eishockey',u'NHL',u'ÄÖÜ',u'éèê',u'',u'',u'1',u'2.0',u'2.0',u'blabla',1,u'1-0',),(None,1,datetime.datetime.today(),datetime.datetime.today(),u'',u'Eishockey',u'NHL',u'heimteam1',u'awayteam1',u'',u'',u'1',u'2.0',u'2.0',u'blabla',0,u'0-1',),(None,1,datetime.datetime.today(),datetime.datetime.today(),u'',u'Eishockey',u'NHL',u'heimteam1',u'awayteam1',u'',u'',u'1',u'2.0',u'2.0',u'blabla',1,u'1-0',),(None,1,datetime.datetime.today(),datetime.datetime.today(),u'',u'Eishockey',u'NHL',u'heimteam1',u'awayteam1',u'',u'',u'1',u'2.0',u'2.0',u'blabla',0,u'0-1',),(None,1,datetime.datetime.today(),datetime.datetime.today(),u'',u'Eishockey',u'NHL',u'heimteam1',u'awayteam1',u'',u'',u'1',u'3.0',u'3.0',u'blabla',1,u'1-0',),(None,1,datetime.datetime.today(),datetime.datetime.today(),u'',u'Eishockey',u'NHL',u'heimteam1',u'awayteam1',u'',u'',u'1',u'2.0',u'2.0',u'blabla',1,u'1-0',)]
        for i in picks:
            cursor.execute(u'''insert into picks values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''',i)
        #print 'Inserted Testdata picks'
        bets = [(None,1,u'2,14',datetime.datetime.today(),1,u'1',u'Normal',2.0,5,1,10,),(None,1,u'3',datetime.datetime.today(),1,u'2',u'Normal',2.0,5,0,0,),(None,1,u'4,15',datetime.datetime.today(),1,u'3',u'Normal',2.0,10,1,20,),(None,1,u'5',datetime.datetime.today(),1,u'4',u'Normal',2.0,50,0,0,),(None,1,u'6,16',datetime.datetime.today(),1,u'5',u'Normal',3.0,5,1,15,),(None,1,u'7,17',datetime.datetime.today(),1,u'6',u'Normal',2.0,5,1,10,)]
        for i in bets:
            cursor.execute(u'''insert into bets values (?,?,?,?,?,?,?,?,?,?,?)''',i)
        #print 'Inserted Testdata bets'
        self.ramdb.commit()
        #print 'Inserted Testdata'
        self.CloseCursor(cursor)
        
    def CopyDB(self,ActiveDB,BackupDB=None,way=True):
        #function to from which db to copy data to another db
        #way: Copy to or from RamDB: True copies RamDB to disk, False copies DiskDB to ram
        if BackupDB != None:
            #backupdb provided --> make a backup of the db
            #delete file if it already exists
            try:
                os.remove(BackupDB)
                #print u'deleted file'
            except:
                print u'file was already deleted'
            try:
                #try because user might not have write rights
                diskdb = sqlite3.connect(ActiveDB)
                diskbackupdb = sqlite3.connect(BackupDB)
            except sqlite3.OperationalError:
                print u'Unable to open file. Not enough rights?'
            try:
                #try because Password might not be defined
                if Password != '':
                    #password is provided and not '', so make an encrypted copy
                    #!add encryptfunction
                    print u'Password protected DB'
                else:
                    #password = '' --> no password, don't encrypt
                    #print u'No Password'
                    self.CopyProcess(diskdb,diskbackupdb)   
            except:
                #Password not defined, don't encrypt
                #print 'No Password'
                self.CopyProcess(diskdb,diskbackupdb)
        else:
            #backupdb not provided, copy to or from ramdb, depending on way
            try:
                #try because user might not have write rights
                diskdb = sqlite3.connect(ActiveDB)
            except sqlite3.OperationalError:
                print u'Unable to open file. Not enough rights?'
            try:
                #try because Password might not be defined
                if Password != '':
                    #password is provided and not '', so make an encrypted copy
                    #!add encryptfunction
                    print u'Password protected DB'
                else:
                    #password = '' --> no password, don't encrypt
                    #print u'No Password'
                    if way == True:
                        #copy RamDB to Disk
                        self.CloseConnection(diskdb)
                        #delete file if it already exists
                        try:
                            os.remove(ActiveDB)
                            #print u'deleted file'
                        except:
                            print u'file was already deleted'
                        diskdb = sqlite3.connect(ActiveDB)
                        self.CopyProcess(self.ramdb,diskdb) 
                    elif way == False:
                        #copy DiskDB to Ram
                        self.CopyProcess(diskdb,self.ramdb)
                    else:
                        raise ValueError
            except:
                #Password not defined, don't encrypt
                #print u'No Password'
                if way == True:
                    #copy RamDB to Disk
                    self.CloseConnection(diskdb)
                    #delete file if it already exists
                    try:
                        os.remove(ActiveDB)
                        #print u'deleted file'
                    except:
                        print u'file was already deleted'
                    diskdb = sqlite3.connect(ActiveDB)
                    self.CopyProcess(self.ramdb,diskdb)
                elif way == False:
                    #copy DiskDB to Ram
                    self.CopyProcess(diskdb,self.ramdb)
                else:
                    raise ValueError
        
    def CopyProcess(self,SourceDB,DestinationDB):
        #function to copy SourceDB to DestinationDB
        query = u''
        for i in SourceDB.iterdump():
            query += i
        DestinationDB.executescript(query)
        DestinationDB.commit()
        if SourceDB != self.ramdb and DestinationDB != self.ramdb:
            self.CloseConnection(SourceDB)
            self.CloseConnection(DestinationDB)
            #print 'closed connection (source)'
            #print 'closed connection (destination)'
        elif SourceDB != self.ramdb and DestinationDB == self.ramdb:
            self.CloseConnection(SourceDB)
            #print 'closed connection (source)'
        elif SourceDB == self.ramdb and DestinationDB != self.ramdb:
            self.CloseConnection(DestinationDB)
            #print 'closed connection (destination)'
        #print 'copied db'

    def CloseCursor(self,cursor):
        #function to close a cursor
        cursor.close()
        #print 'closed cursor'
        
    def CloseConnection(self,connection):
        #function to close a connection
        connection.close()
        #print 'closed connection'
        
    def InsertData(self,table,data):
        #function to insert data into table
        def AssembleQuery(length):
            #function to make as many (?,?,etc.) as data has colums to insert to 
            a = u'''(?'''
            while length > 1:
                a += u''',?'''
                length -= 1
            a += u''')'''
            return a
        
        length = len(data[0])
        query = AssembleQuery(length)
        cursor = self.ramdb.cursor()
        for i in data:
            cursor.execute(u'''insert into '''+unicode(table)+u''' values '''+query,i)
        self.CloseCursor(cursor)
        self.ramdb.commit()
        print 'inserted data'
        
    def GetData(self,table,where=None,what=None):
        #function to get data from the db
        cursor = self.ramdb.cursor()
        if where == None:
            cursor.execute(u'''select * from '''+table)
            data = cursor.fetchall()
            self.CloseCursor(cursor)
            #print data
            return data
        else:
            cursor.execute(u'''select * from '''+table+u''' where '''+where+u'''=?''',what)
            data = cursor.fetchall()
            self.CloseCursor(cursor)
            #print data
            return data
        
    def UpdateData(self,table,indexname,index,text,column):
        textindex = (unicode(text),index,)
        cursor = self.ramdb.cursor()
        cursor.execute(u'''update '''+unicode(table)+''' set '''+unicode(column+u' = ?')+''' where '''+unicode(indexname+' = ?' ),textindex)

        

#!For testing purposes making the file run on its own and calling the functions
def Main():
    a = SQLiteDB()
    #copy diskdb to ramdb test
    #a.CopyDB('C:\\Users\\Blaicke\\Documents\\test.sqlite',None,False)
    #try:
        #os.remove('C:\\Users\\Blaicke\\Documents\\test.sqlite')
        #print 'deleted file'
    #except:
        #print 'file was already deleted'
    a.MakeTables()
    a.CategoryETCData()
    a.TestData()
    #a.InsertData('sportleague',[('Lala','La',),('abc','ab',)])
    #a.InsertData('bookies',[(None,u'bet365',0.00,0.00,00.00,0.00,0,0,0,0,u'bg186867',u'hättestewohlgerne',),(None,u'betfair',0.00,0.00,0.00,0.00,0,0,0,0,u'bg1404',u'hättestewohlgerne',)])
    a.UpdateData(u'bookies',u'bookieid',1,u'www.pinnaclesports.com',u'name')
    #b = a.GetData(u'bookies')
    #print b[0][1]
    #a.ramdb.commit()
    a.ramdb.rollback()
    #b = a.GetData(u'bookies')
    #print b[0][1]
    #a.GetData('transactions','bookieid','2')
    #a.CopyDB('C:\\Users\\Blaicke\\Documents\\test.sqlite')
    #backup diskdb test
    #a.CopyDB('C:\\Users\\Blaicke\\Documents\\test.sqlite','C:\\Users\\Blaicke\\Documents\\test4.sqlite')

    
if __name__ == u'__main__':
    Main()
